package com.campus.server.dao;

import com.campus.common.util.DBUtil;
import com.campus.common.vo.AcademicRecord;
import com.campus.common.vo.Student;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentAccountManagementDao {
    // 获取所有书籍
    public List<Student> listAllStudents() {
        List<Student> students = new ArrayList<>();
        String sql = "SELECT * FROM student1";

        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                students.add(new Student(
                        rs.getString("StudentId"),
                        rs.getString("name"),
                        new AcademicRecord(
                                rs.getString("recordId"),
                                rs.getString("sex"),
                                rs.getInt("age"),
                                rs.getString("grade"),
                                rs.getString("major"),
                                rs.getString("className"),
                                rs.getString("status"),
                                rs.getString("enrollDate"),
                                rs.getString("contactInfo")
                        )
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return students;
    }


    // 新增学生
    public boolean addStudent(Student student) {
        String sql = "INSERT INTO student1 (studentId, recordId, name, age, sex, grade, " +
                "className, major, status, enrollDate, contactInfo) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, student.getStudentId());
            stmt.setString(2, student.getAcademicRecord().getRecordId());
            stmt.setString(3, student.getStudentName());
            stmt.setInt(4, student.getAcademicRecord().getAge());
            stmt.setString(5, student.getAcademicRecord().getSex());
            stmt.setString(6, student.getAcademicRecord().getGrade());
            stmt.setString(7, student.getAcademicRecord().getClassName());
            stmt.setString(8, student.getAcademicRecord().getMajor());
            stmt.setString(9, student.getAcademicRecord().getStatus());
            stmt.setString(10, student.getAcademicRecord().getEnrollDate());
            stmt.setString(11, student.getAcademicRecord().getContactInfo());

            int rowsAffected_1 = stmt.executeUpdate();
            return rowsAffected_1 > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 删除学生
    public boolean deleteStudent(String studentId) {
        String sql_1="DELETE FROM user1 WHERE username=?";

        String sql_2 = "DELETE FROM student1 WHERE studentId = ?";

        int rowsAffected_1=0;
        int rowsAffected_2=0;

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt_1 = conn.prepareStatement(sql_1);
             PreparedStatement stmt_2 = conn.prepareStatement(sql_2)) {

            stmt_1.setString(1, studentId);
            stmt_2.setString(1, studentId);
            rowsAffected_1 = stmt_1.executeUpdate();
            rowsAffected_2 = stmt_2.executeUpdate();
            System.out.println("studentId=" + studentId);
            System.out.println("rowsAffected = " + rowsAffected_1);
            System.out.println(rowsAffected_1>0);

            return rowsAffected_1>0 && rowsAffected_2>0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }

    }

    // 根据ID查询书籍
    public Student getStudentById(String studentId) {
        String sql = "SELECT * FROM student1 WHERE studentId = ?";
        System.out.println("hi");

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, studentId);
            ResultSet rs = stmt.executeQuery();
            System.out.println(studentId);

            if (rs.next()) {
                System.out.println(studentId);
                return new Student(
                        rs.getString("StudentId"),
                        rs.getString("name"),
                        new AcademicRecord(
                                rs.getString("recordId"),
                                rs.getString("sex"),
                                rs.getInt("age"),
                                rs.getString("grade"),
                                rs.getString("major"),
                                rs.getString("className"),
                                rs.getString("status"),
                                rs.getString("enrollDate"),
                                rs.getString("contactInfo")
                        )
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("Not found");
        return null;
    }

    // 根据书名查询书籍（模糊查询）
    public List<Student> searchStudentsById(String studentId) {
        List<Student> students = new ArrayList<>();
        String sql = "SELECT * FROM student1 WHERE studentId LIKE ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, "%" + studentId + "%");
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                students.add(new Student(
                        rs.getString("StudentId"),
                        rs.getString("name"),
                        new AcademicRecord(
                                rs.getString("recordId"),
                                rs.getString("sex"),
                                rs.getInt("age"),
                                rs.getString("grade"),
                                rs.getString("major"),
                                rs.getString("className"),
                                rs.getString("status"),
                                rs.getString("enrollDate"),
                                rs.getString("contactInfo")
                        )
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return students;
    }

    // 根据作者查询书籍（模糊查询）
    public List<Student> searchStudentsByName(String name) {
        List<Student> students = new ArrayList<>();
        String sql = "SELECT * FROM student1 WHERE name LIKE ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, "%" + name + "%");
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                students.add(new Student(
                        rs.getString("StudentId"),
                        rs.getString("name"),
                        new AcademicRecord(
                                rs.getString("recordId"),
                                rs.getString("sex"),
                                rs.getInt("age"),
                                rs.getString("grade"),
                                rs.getString("major"),
                                rs.getString("className"),
                                rs.getString("status"),
                                rs.getString("enrollDate"),
                                rs.getString("contactInfo")
                        )
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return students;
    }

    // 综合查询（根据书名和作者模糊查询）
    public List<Student> searchStudents(String studentId, String name) {
        List<Student> students = new ArrayList<>();

        // 构建动态SQL
        StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM student1 WHERE 1=1");
        List<Object> params = new ArrayList<>();

        if (studentId != null && !studentId.trim().isEmpty()) {
            sqlBuilder.append(" AND studentId LIKE ?");
            params.add("%" + studentId + "%");
        }

        if (name != null && !name.trim().isEmpty()) {
            sqlBuilder.append(" AND name LIKE ?");
            params.add("%" + name + "%");
        }

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sqlBuilder.toString())) {

            // 设置参数
            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                students.add(new Student(
                        rs.getString("StudentId"),
                        rs.getString("name"),
                        new AcademicRecord(
                                rs.getString("recordId"),
                                rs.getString("sex"),
                                rs.getInt("age"),
                                rs.getString("grade"),
                                rs.getString("major"),
                                rs.getString("className"),
                                rs.getString("status"),
                                rs.getString("enrollDate"),
                                rs.getString("contactInfo")
                        )
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return students;
    }

    // 更新书籍信息
    public boolean updateStudent(Student student) {
        String sql = "UPDATE student1 SET recordId = ?, name = ?, age = ?, sex = ?, grade = ?," +
                " className = ?, major = ?, status = ?, enrollDate = ?, contactInfo = ? WHERE studentId = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(11, student.getStudentId());
            stmt.setString(1, student.getAcademicRecord().getRecordId());
            stmt.setString(2, student.getStudentName());
            stmt.setInt(3, student.getAcademicRecord().getAge());
            stmt.setString(4, student.getAcademicRecord().getSex());
            stmt.setString(5, student.getAcademicRecord().getGrade());
            stmt.setString(6, student.getAcademicRecord().getClassName());
            stmt.setString(7, student.getAcademicRecord().getMajor());
            stmt.setString(8, student.getAcademicRecord().getStatus());
            stmt.setString(9, student.getAcademicRecord().getEnrollDate());
            stmt.setString(10, student.getAcademicRecord().getContactInfo());

            int rowsAffected = stmt.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 检查书籍是否存在
    public boolean studentExists(String studentId) {
        String sql = "SELECT COUNT(*) FROM student1 WHERE studentId = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, studentId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
}
